﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using F_MODEL;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
namespace F_DAL
{
    public class UserInfoDal
    {
        string ConnectionSting = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();


        /// <summary>
        /// 余额查询
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public List<UserInfo> UserInfoMoneySel(int userId)
        {
            SqlParameter[] paramter ={
                               new  SqlParameter("@userId",SqlDbType.Int,50){Value=userId}
                               };
            SqlDataReader read = DbHelperSQL.RunProcedure("dbo.Pro_YuE", paramter);
            List<UserInfo> list = new List<UserInfo>();
            while (read.Read())
            {
                UserInfo us = new UserInfo();
                us.bankName = read["bankName"].ToString();
                us.cardNum = read["cardNum"].ToString();
                us.transactionMoney = Convert.ToDecimal(read["transactionMoney"].ToString());
                us.surplusMoney = Convert.ToDecimal(read["surplusMoney"].ToString());
                us.youhui = Convert.ToInt32(read["youhui"].ToString());
                us.upcomingMoney = Convert.ToDecimal(read["upcomingMoney"].ToString());
                us.userId = Convert.ToInt32(read["userId"].ToString());
                list.Add(us);
            }
            return list;
        }

        /// <summary>
        /// 关于我们
        /// </summary>
        /// <returns></returns>
        public List<About> AboutUsList()
        {
            SqlConnection conn = new SqlConnection(ConnectionSting);
            conn.Open();
            string sql = "select * from About";
            List<About> list = new List<About>();
            #region
            SqlCommand command = new SqlCommand(sql, conn);
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                About mod = new About();
                mod.publicWechat = reader["publicWechat"].ToString();
                mod.email = reader["email"].ToString();
                mod.wechat = reader["wechat"].ToString();
                mod.tel = reader["tel"].ToString();
                list.Add(mod);
            }
            conn.Close();
            #endregion
            return list;
        }

        /// <summary>
        /// 飞人个人中心
        /// </summary>
        /// <param name="id">用户编号</param>
        /// <returns></returns>
        public List<UserInfo> UserInfo_EditShow(int id)
        {
            SqlConnection conn = new SqlConnection(ConnectionSting);
            conn.Open();
            List<UserInfo> list = new List<UserInfo>();
            #region
            SqlCommand command = new SqlCommand("Super_Show", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;
            //参数
            SqlParameter p1 = new SqlParameter("@userId", System.Data.SqlDbType.Int);
            p1.Value = id;
            command.Parameters.Add(p1);
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                UserInfo mod = new UserInfo();
                mod.name = reader["name"].ToString();
                mod.sex = Convert.ToInt32(reader["sex"].ToString());
                mod.birthday = Convert.ToDateTime(reader["birthday"]);
                mod.job = reader["job"].ToString();
                mod.passport = reader["passport"].ToString();
                mod.cnTel = reader["cnTel"].ToString();
                mod.foreignTel = reader["foreignTel"].ToString();
                mod.wechat = reader["wechat"].ToString();
                mod.creditScore = Convert.ToInt32(reader["creditScore"]);
                list.Add(mod);
            }
            conn.Close();
            #endregion
            return list;
        }

        //连接数据库字符串
        /// <summary>
        /// 意见反馈添加
        /// </summary>
        /// <param name="back">问题</param>
        /// <param name="img">图片</param>
        /// <param name="pho">手机号</param>
        /// <returns></returns>
        public int Opinions_ADD(string back, string img, string pho)
        {
            SqlConnection conn = new SqlConnection(ConnectionSting);
            SqlCommand command = new SqlCommand("suggest_advise", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;
            SqlParameter p1 = new SqlParameter("contents", System.Data.SqlDbType.VarChar);
            p1.Value = back;
            SqlParameter p2 = new SqlParameter("img", System.Data.SqlDbType.VarChar);
            p2.Value = img;
            SqlParameter p3 = new SqlParameter("phone", System.Data.SqlDbType.VarChar);
            p3.Value = pho;
            command.Parameters.Add(p1);
            command.Parameters.Add(p2);
            command.Parameters.Add(p3);
            conn.Open();
            int i = command.ExecuteNonQuery();
            return i;
        }

        //连接数据库字符串

        /// <summary>
        /// 根据ID编号来显示不同的问题
        /// </summary>
        /// <param name="P_ID">条件ID</param>
        /// <returns></returns>
        public Problems problems_show(int P_ID)
        {
            SqlConnection conn = new SqlConnection(ConnectionSting);
            SqlCommand command = new SqlCommand("problem_mi", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;
            SqlParameter p1 = new SqlParameter("ID_problem", System.Data.SqlDbType.Int);
            p1.Value = P_ID;
            command.Parameters.Add(p1);
            conn.Open();
            SqlDataReader read = command.ExecuteReader();
            Problems p = new Problems();
            if (read.Read())
            {
                p.problemId = Convert.ToInt32(read["problemId"]);
                p.problemTitle = read["problemTitle"].ToString();
                p.problemContent = read["problemContent"].ToString();
                p.problemIsDel = Convert.ToInt32(read["problemIsDel"]);
                p.problemTimeSpan = Convert.ToDateTime(read["problemTimeSpan"]);
            }
            conn.Close();
            return p;

        }

        /// <summary>
        /// 问题的显示
        /// </summary>
        /// <returns></returns>
        public List<Problems> problems_index()
        {
            SqlConnection conn = new SqlConnection(ConnectionSting);
            SqlCommand command = new SqlCommand("problems_show", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;
            conn.Open();
            SqlDataReader read = command.ExecuteReader();
            List<Problems> list = new List<Problems>();
            while (read.Read())
            {
                Problems p = new Problems();
                p.problemId = Convert.ToInt32(read["problemId"]);
                p.problemTitle = read["problemTitle"].ToString();
                p.problemContent = read["problemContent"].ToString();
                p.problemIsDel = Convert.ToInt32(read["problemIsDel"]);
                p.problemTimeSpan = Convert.ToDateTime(read["problemTimeSpan"]);
                list.Add(p);
            }
            conn.Close();
            return list;

        }

        /// <summary>
        /// 成为飞人
        /// </summary>
        /// <param name="fm">FlyMan  成为飞人需要的数据</param>
        /// <returns>是否成功</returns>
        public int UserBeFlyMan_zsh(Tool_BeFlyMan_zsh fm)
        {

            string sql = @"update [dbo].[UserInfo] set 
      [cnTel]='" + fm.cntel + @"'
      ,[foreignTel]='" + fm.foreignTel + @"'
      ,[sex]=" + fm.sex + @"
      ,[birthday]='" + fm.birthday + @"'
	  ,[job]='" + fm.job + @"'
      ,[name]='" + fm.name + @"'
      ,[passport]='" + fm.passport + @"'
      ,[passportImg]='" + fm.passportImg + @"'
      ,[creditScore]=0
	  ,[isFlyman]=1
      where  [userId]= " + fm.userId + "";
            int i = DBHelper.ExecuteSql(sql);
            return i;
        }


        /// <summary>
        /// 用户登录  用手机号和密码验证
        /// </summary>
        /// <param name="usertel">手机号码</param>
        /// <param name="password">密码</param>
        /// <returns></returns>
        public UserInfo zyl_Login_dal(string usertel, string password)
        {
            //连接数据库
            SqlConnection conn = new SqlConnection(ConnectionSting);
            conn.Open();//打开数据连接
            SqlCommand command = new SqlCommand("userLogin", conn);//执行存储过程
            //-----------------------注意这一段--------------------------------------------------------------------------------------------------------
            SqlParameter p1 = new SqlParameter("@tel", System.Data.SqlDbType.VarChar);
            p1.Value = usertel;
            SqlParameter p2 = new SqlParameter("@password", System.Data.SqlDbType.VarChar);
            p2.Value = password;
            command.Parameters.Add(p1);
            command.Parameters.Add(p2);
            command.CommandType = System.Data.CommandType.StoredProcedure;

            //-------------------------------------------------------------------------------------------------------------------------------


            SqlDataReader reader = command.ExecuteReader();
            //执行所读取到的数据

            UserInfo mod = new UserInfo();

            while (reader.Read())
            {
                mod.userId = Convert.ToInt32(reader["userId"]);
                mod.userImg = reader["userImg"].ToString();
                mod.cnTel = reader["cnTel"].ToString();
                mod.foreignTel = reader["foreignTel"].ToString();

                if (reader["sex"].GetType() != typeof(DBNull))
                {
                    mod.sex = Convert.ToInt32(reader["sex"]);
                }
                else
                {
                    mod.sex = -1;
                }
                if (reader["birthday"].GetType() != typeof(DBNull))
                {
                    mod.birthday = Convert.ToDateTime(reader["birthday"]);
                }
                else
                {
                    mod.birthday = Convert.ToDateTime("1900-01-01");
                }
                mod.email = reader["email"].ToString();
                mod.wechat = reader["wechat"].ToString();
                mod.job = reader["job"].ToString();
                mod.name = reader["name"].ToString();
                mod.passport = reader["passport"].ToString();
                mod.passportImg = reader["passportImg"].ToString();
                if (reader["creditScore"].GetType() != typeof(DBNull))
                {
                    mod.creditScore = Convert.ToInt32(reader["creditScore"]);
                }
                else
                {
                    mod.creditScore = -1;
                }
                if (reader["isFlyman"].GetType() != typeof(DBNull))
                {
                    mod.isFlyman = Convert.ToInt32(reader["isFlyman"]);
                }
                else
                {
                    mod.isFlyman = -1;
                }
                if (reader["userIsDel"].GetType() != typeof(DBNull))
                {
                    mod.userIsDel = Convert.ToInt32(reader["userIsDel"]);
                }
                else
                {
                    mod.userIsDel = -1;
                }
                if (reader["userTimeSpan"].GetType() != typeof(DBNull))
                {
                    mod.userTimeSpan = Convert.ToDateTime(reader["userTimeSpan"]);
                }
                else
                {
                    mod.userTimeSpan = Convert.ToDateTime("1900-01-01");
                }
                mod.password = reader["password"].ToString();
            }

            conn.Close();
            return mod;//返回单条记录



        }
        /// <summary>
        /// 注册
        /// </summary>
        /// <param name="usertel">联系方式</param>
        /// <param name="password">密码</param>
        /// <returns></returns>
        public int zyl_Regist_dal(string usertel, string password)
        {
            //连接数据库
            SqlConnection conn = new SqlConnection(ConnectionSting);
            SqlCommand command = new SqlCommand("userRegist", conn);//执行存储过程
            //-----------------------注意这一段--------------------------------------------------------------------------------------------------------
            SqlParameter p1 = new SqlParameter("@usertel", System.Data.SqlDbType.VarChar);
            p1.Value = usertel;
            SqlParameter p2 = new SqlParameter("@userpwd", System.Data.SqlDbType.VarChar);
            p2.Value = password;
            SqlParameter p3 = new SqlParameter("@userid", System.Data.SqlDbType.Int);
            p3.Direction = System.Data.ParameterDirection.Output;
            command.Parameters.Add(p1);
            command.Parameters.Add(p2);
            command.Parameters.Add(p3);
            command.CommandType = System.Data.CommandType.StoredProcedure;
            //-------------------------------------------------------------------------------------------------------------------------------

            conn.Open();//打开数据连接
            command.ExecuteScalar();
            int sign = Convert.ToInt32(p3.Value);

            conn.Close();
            return sign;//返回单条记录


        }

        /// <summary>
        /// 修改密码
        /// </summary>
        /// <param name="usertel">联系方式</param>
        /// <param name="password">密码</param>
        /// <returns></returns>
        public int zyl_UpdatePwd_dal(string usertel, string password)
        {
            //连接数据库
            SqlConnection conn = new SqlConnection(ConnectionSting);
            SqlCommand command = new SqlCommand("update_userpwd", conn);//执行存储过程
            //-----------------------注意这一段--------------------------------------------------------------------------------------------------------
            SqlParameter p1 = new SqlParameter("@tel", System.Data.SqlDbType.VarChar);
            p1.Value = usertel;
            SqlParameter p2 = new SqlParameter("@password", System.Data.SqlDbType.VarChar);
            p2.Value = password;
            SqlParameter p3 = new SqlParameter("@result", System.Data.SqlDbType.Int);
            p3.Direction = System.Data.ParameterDirection.Output;
            command.Parameters.Add(p1);
            command.Parameters.Add(p2);
            command.Parameters.Add(p3);
            command.CommandType = System.Data.CommandType.StoredProcedure;
            //-------------------------------------------------------------------------------------------------------------------------------

            conn.Open();//打开数据连接
            command.ExecuteScalar();
            int sign = Convert.ToInt32(p3.Value);

            conn.Close();
            return sign;//返回单条记录


        }

    }
}
